
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('[dbo].[AddFeedInfo]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[AddFeedInfo]
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('[dbo].[AddRow]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[AddRow]
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('[dbo].[GetAllRssFeedInfo]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[GetAllRssFeedInfo]
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('[dbo].[GetFeedConfig]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[GetFeedConfig]
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('[dbo].[GetFeedGuid]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[GetFeedGuid]
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('[dbo].[UpdateFeedConfigString]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[UpdateFeedConfigString]
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('[dbo].[CreateRssReaderRole]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[CreateRssReaderRole]
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('[dbo].[CreateRssWriterRole]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[CreateRssWriterRole]
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('[dbo].[RssFeedInfo]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
BEGIN
	DROP TABLE [dbo].[RssFeedInfo]
END
GO

/*
Adds a new row with autogenerated FeedGuid to the RssFeedInfo table.
*/
CREATE PROCEDURE AddFeedInfo ( @feedConfigString ntext, @userGuid uniqueidentifier )
AS
INSERT INTO [dbo].[RssFeedInfo]([FeedInfo], [UserGuid], [FeedGuid])
VALUES(@feedConfigString, @userGuid, NEWID())
GO

/*
Add a new row to the RssFeedInfo table.
*/
CREATE PROCEDURE AddRow ( @feedConfigString ntext, @userGuid uniqueidentifier, @feedGuid uniqueidentifier )
AS
INSERT INTO [dbo].[RssFeedInfo]([FeedInfo], [UserGuid], [FeedGuid])
VALUES (@feedConfigString, @userGuid, @feedGuid)
GO

/*
Gets all entries in the RssFeedInfo table
*/
CREATE PROCEDURE GetAllRssFeedInfo
AS
SELECT * FROM [dbo].[RssFeedInfo]
GO

/*
Gets the FeedInfo text by using the FeedGuid
*/
CREATE PROCEDURE GetFeedConfig ( @feedGuid uniqueidentifier )
AS
SELECT [dbo].[RssFeedInfo].[FeedInfo] 
FROM [dbo].[RssFeedInfo]
WHERE [dbo].[RssFeedInfo].[FeedGuid] = @feedGuid
GO

/*
Get the FeedGuid using the UserGuid
*/
CREATE PROCEDURE GetFeedGuid ( @userGuid uniqueidentifier )
AS
IF EXISTS (SELECT * FROM [dbo].[RssFeedInfo] WHERE [dbo].[RssFeedInfo].[UserGuid] = @userGuid)
	SELECT [dbo].[RssFeedInfo].[FeedGuid]
	FROM [dbo].[RssFeedInfo]
	WHERE [dbo].[RssFeedInfo].[UserGuid] = @userGuid
ELSE
	EXEC [AddFeedInfo] '', @userGuid
	SELECT [dbo].[RssFeedInfo].[FeedGuid]
	FROM [dbo].[RssFeedInfo]
	WHERE [dbo].[RssFeedInfo].[UserGuid] = @userGuid
GO

/*
Updates the user's FeedInfo
*/
CREATE PROCEDURE UpdateFeedConfigString ( @feedConfigString ntext, @userGuid uniqueidentifier )
AS
IF EXISTS (SELECT * FROM [dbo].[RssFeedInfo] WHERE [UserGuid] = @userGuid)
	UPDATE [dbo].[RssFeedInfo]
	SET [FeedInfo] = @feedConfigString
	WHERE [UserGuid] = @userGuid
ELSE
	EXEC [dbo].[AddFeedInfo] @feedConfigString, @userGuid
GO

/*
Create a Rss_Reader role
*/
CREATE PROCEDURE CreateRssReaderRole
AS
IF NOT EXISTS (SELECT '*' FROM dbo.SysUsers WHERE name = N'Rss_Reader' AND IsSQLRole = 1)
BEGIN
	EXEC sp_addrole    N'Rss_Reader'
END
GRANT EXEC ON GetFeedConfig TO Rss_Reader
GRANT EXEC ON GetFeedGuid TO Rss_Reader
GO

CREATE PROCEDURE CreateRssWriterRole
AS
IF NOT EXISTS (SELECT '*' FROM dbo.SysUsers WHERE name = N'Rss_Writer' AND IsSQLRole = 1)
BEGIN
	EXEC sp_addrole    N'Rss_Writer'
END
GRANT EXEC ON AddFeedInfo TO Rss_Writer
GRANT EXEC ON AddRow TO Rss_Writer
GRANT EXEC ON UpdateFeedConfigString TO Rss_Writer
GO

/*
Creates the Rss Feed Info table.
*/
CREATE TABLE RssFeedInfo
(
	FeedInfo ntext,
	UserGuid uniqueidentifier NOT NULL,
	FeedGuid uniqueidentifier NOT NULL PRIMARY KEY
)
GO

CREATE UNIQUE NONCLUSTERED INDEX RssFeedInfo_UserGuid
ON RssFeedInfo (UserGuid)
GO

CREATE UNIQUE NONCLUSTERED INDEX RssFeedInfo_FeedGuid
ON RssFeedInfo (FeedGuid)
GO

EXEC CreateRssReaderRole
EXEC CreateRssWriterRole